﻿using INTEX.DataBase;
using INTEX.DataBase.Tools;
using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.IO;
using System.Linq;
using System.Windows;
namespace WPFExcelReport
{
    class JLTFWB18Report : BaseReport
    {
        #region ----------------PROPERTIES----------------------------------
        public OracleConnection OracleDB;
        public OracleCommand orlReadCommand = new OracleCommand();
        public IntexReptServBLClient proxy;
        private Dictionary<string, object> dicParamater;
        public DataSet dsResult;
        public DataSet setUI = new DataSet();
        string ABPAUFFKRS;
        string ABPAUFFLFD;
        string ABPAUFFPOS;
        string DocType;
        string CellName = "";
        string CellValue = ""; 
        public string LANGUAGE = "",COMBO = "";
        string strCompany;
        string strExcelFile;
        Dictionary<string, int> qpplan = new Dictionary<string, int>();
        
        #endregion
        public JLTFWB18Report()
            : base()
        {
        }
        public JLTFWB18Report(Dictionary<string, object> dicParamater)
            : base(dicParamater)
        {
            strCompany = dicParamater["Company"].ToString().Trim();
            strExcelFile = dicParamater["ExcelFile"].ToString().Trim();
            if (dicParamater.ContainsKey("ROOTDRIVE"))
            {
                Root = dicParamater["ROOTDRIVE"].ToString();
            }
            else
            {
                Root = "C:\\IntexReport";
            }
                      						

        } 
        #region------------------------------Function------------------------
        /*ART	COLOR	WBBPACKNR	WIDTH	WBFKNEUDAT	WBFKWARSCH	WBFKWMPOS	MTR	YARD	TOTRATE	YRATE	
                    BEM	WBFFSCHL DES	RATE	TYPE	FDEFECT	EDEFECT*/
        public void FillInExcelData(DataSet setSoruce, int xStart = 2, int yStart = 1)
        {
            try
            {
                int x = xStart, y = yStart;
                DataTable dtSoruce = setSoruce.Tables["WB18"];                
                DataTable dtOutput = new DataTable();
                dtOutput.Columns.Add("ART", typeof(System.String));
                dtOutput.Columns.Add("COLOR", typeof(System.String));
                dtOutput.Columns.Add("WBBLB", typeof(System.String));
                dtOutput.Columns.Add("WBBSTKNR", typeof(System.String));
                dtOutput.Columns.Add("WBBPACKNR", typeof(System.String));
                dtOutput.Columns.Add("WBFKWARSCH", typeof(System.String));
                dtOutput.Columns.Add("WBFKWMPOS", typeof(System.String));
                dtOutput.Columns.Add("MTR", typeof(System.String));
                dtOutput.Columns.Add("YARD", typeof(System.String));
                dtOutput.Columns.Add("TOTRATE", typeof(System.String));
                dtOutput.Columns.Add("DES", typeof(System.String));
                dtOutput.Columns.Add("RATE", typeof(System.String));
                dtOutput.Columns.Add("TYPE", typeof(System.String));
                dtOutput.Columns.Add("FDEFECT", typeof(System.String));
                dtOutput.Columns.Add("EDEFECT", typeof(System.String));
                dtOutput.Columns.Add("WIDTH", typeof(System.String));
                dtOutput.Columns.Add("SKEW", typeof(System.String));
                dtOutput.Columns.Add("BOW", typeof(System.String));
                dtOutput.Columns.Add("YRATE", typeof(System.String));
                dtOutput.Columns.Add("BEM", typeof(System.String));
                dtOutput.Columns.Add("WBFKNEUDAT", typeof(System.String));
                dtOutput.Columns.Add("JUDGE", typeof(System.String));
                dtOutput.Merge(dtSoruce, false, MissingSchemaAction.Ignore);

                exAgent.DataTableToExcel(dtOutput, 3, 1);

                int count = dtOutput.Rows.Count;
                exAgent.SetCellValue(1, 1, 1);
                exAgent.BatchConvert2Number(3, 8, count + 2, 10);
                exAgent.BatchConvert2Number(3, 12, count + 2, 12);
                exAgent.BatchConvert2Number(3, 14, count + 2, 15);
                if (LANGUAGE == "V")
                {
                    exAgent.SetCellValue(1, 1, "金陵疵点统计表");
                }
                if (LANGUAGE == "E")
                {
                    exAgent.SetCellValue(1, 1, "Jinling Defect Summary Report");
                }
                						
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        public void FillInExcel(DataSet setSoruce, int xStart = 2, int yStart = 1)
        {
            try
            {
                int x = xStart, y = yStart;
                DataTable dtSoruce = setSoruce.Tables["WB18"];
                DataTable dtOutput = new DataTable();
                dtOutput.Columns.Add("ART", typeof(System.String));
                dtOutput.Columns.Add("COLOR", typeof(System.String));
                dtOutput.Columns.Add("WBBLB", typeof(System.String));
                dtOutput.Columns.Add("WBBSTKNR", typeof(System.String));
                dtOutput.Columns.Add("WBBPACKNR", typeof(System.String));
                dtOutput.Columns.Add("WBFKWARSCH", typeof(System.String));
                dtOutput.Columns.Add("WBFKWMPOS", typeof(System.String));
                dtOutput.Columns.Add("MTR", typeof(System.String));
                dtOutput.Columns.Add("YARD", typeof(System.String));
                dtOutput.Columns.Add("TOTRATE", typeof(System.String));
                dtOutput.Columns.Add("DES", typeof(System.String));
                dtOutput.Columns.Add("RATE", typeof(System.String));
                dtOutput.Columns.Add("TYPE", typeof(System.String));
                dtOutput.Columns.Add("FDEFECT", typeof(System.String));
                dtOutput.Columns.Add("EDEFECT", typeof(System.String));
                dtOutput.Columns.Add("WIDTH", typeof(System.String));
                dtOutput.Columns.Add("SKEW", typeof(System.String));
                dtOutput.Columns.Add("BOW", typeof(System.String));
                dtOutput.Columns.Add("YRATE", typeof(System.String));
                dtOutput.Columns.Add("BEM", typeof(System.String));
                dtOutput.Columns.Add("WBFKNEUDAT", typeof(System.String));
                dtOutput.Columns.Add("JUDGE", typeof(System.String));
                dtOutput.Merge(dtSoruce, false, MissingSchemaAction.Ignore);

                exAgent.DataTableToExcel(dtOutput, 3, 1);
                //DataTable dtrSoruce = setSoruce.Tables["WB18"];
                int i = 3;
                string ROLL = "";
                foreach (DataRow dw in dtSoruce.Rows)
                {
                    string WBBSTKNR = dw["WBBSTKNR"].ToString().Trim();
                    if (ROLL == WBBSTKNR)
                    {
                        /*//string WBFFSCHL = dw["WBFFSCHL"].ToString().Trim();
                        //exAgent.SetCellValue(i, 11, WBFFSCHL);
                        string DES = dw["DES"].ToString().Trim();
                        exAgent.SetCellValue(i, 11, DES);
                        string RATE = dw["RATE"].ToString().Trim();
                        exAgent.SetCellValue(i, 12, RATE);
                        string TYPE = dw["TYPE"].ToString().Trim();
                        exAgent.SetCellValue(i, 13, TYPE);
                        string FDEFECT = dw["FDEFECT"].ToString().Trim();
                        exAgent.SetCellValue(i, 14, FDEFECT);
                        string EDEFECT = dw["EDEFECT"].ToString().Trim();
                        exAgent.SetCellValue(i, 15, EDEFECT);
                        string WIDTH = dw["WIDTH"].ToString().Trim();
                        exAgent.SetCellValue(i, 16, WIDTH);
                        string SKEW = dw["SKEW"].ToString().Trim();
                        exAgent.SetCellValue(i, 17, SKEW);
                        string BOW = dw["BOW"].ToString().Trim();
                        exAgent.SetCellValue(i, 18, BOW);    
                         */
                        exAgent.SetCellValue(i, 1, "");
                        exAgent.SetCellValue(i, 2, "");
                        exAgent.SetCellValue(i, 3, "");
                        exAgent.SetCellValue(i, 4, "");
                        exAgent.SetCellValue(i, 5, "");
                        exAgent.SetCellValue(i, 6, "");
                        exAgent.SetCellValue(i, 7, "");
                        exAgent.SetCellValue(i, 8, "");
                        exAgent.SetCellValue(i, 9, "");
                        exAgent.SetCellValue(i, 10, "");
                        exAgent.SetCellValue(i, 19, "");
                        exAgent.SetCellValue(i, 20, "");
                        exAgent.SetCellValue(i, 21, "");
                        exAgent.SetCellValue(i, 22, "");
                    }
                    else
                    {
                        /*string ART = dw["ART"].ToString().Trim();
                        exAgent.SetCellValue(i, 1, ART);
                        string COLOR = dw["COLOR"].ToString().Trim();
                        exAgent.SetCellValue(i, 2, COLOR);
                        string WBBLB = dw["WBBLB"].ToString().Trim();
                        exAgent.SetCellValue(i, 3, WBBLB);
                        exAgent.SetCellValue(i, 4, WBBSTKNR);
                        string WBBPACKNR = dw["WBBPACKNR"].ToString().Trim();
                        exAgent.SetCellValue(i, 5, WBBPACKNR);
                        string WBFKWARSCH = dw["WBFKWARSCH"].ToString().Trim();
                        exAgent.SetCellValue(i, 6, WBFKWARSCH);
                        string WBFKWMPOS = dw["WBFKWMPOS"].ToString().Trim();
                        exAgent.SetCellValue(i, 7, WBFKWMPOS);
                        string MTR = dw["MTR"].ToString().Trim();
                        exAgent.SetCellValue(i, 8, MTR);
                        string YARD = dw["YARD"].ToString().Trim();
                        exAgent.SetCellValue(i, 9, YARD);
                        string TOTRATE = dw["TOTRATE"].ToString().Trim();
                        exAgent.SetCellValue(i, 10, TOTRATE);
                        //string WBFFSCHL = dw["WBFFSCHL"].ToString().Trim();
                        //exAgent.SetCellValue(i, 11, WBFFSCHL);
                        string DES = dw["DES"].ToString().Trim();
                        exAgent.SetCellValue(i, 11, DES);
                        string RATE = dw["RATE"].ToString().Trim();
                        exAgent.SetCellValue(i, 12, RATE);
                        string TYPE = dw["TYPE"].ToString().Trim();
                        exAgent.SetCellValue(i, 13, TYPE);
                        string FDEFECT = dw["FDEFECT"].ToString().Trim();
                        exAgent.SetCellValue(i, 14, FDEFECT);
                        string EDEFECT = dw["EDEFECT"].ToString().Trim();
                        exAgent.SetCellValue(i, 15, EDEFECT);
                        string WIDTH = dw["WIDTH"].ToString().Trim();
                        exAgent.SetCellValue(i, 16, WIDTH);
                        string SKEW = dw["SKEW"].ToString().Trim();
                        exAgent.SetCellValue(i, 17, SKEW);
                        string BOW = dw["BOW"].ToString().Trim();
                        exAgent.SetCellValue(i, 18, BOW);
                        //string AVGWIDTH = dw["AVGWIDTH"].ToString().Trim();
                        //exAgent.SetCellValue(i, 19, AVGWIDTH);
                        string YRATE = dw["YRATE"].ToString().Trim();
                        exAgent.SetCellValue(i, 19, YRATE);
                        string BEM = dw["BEM"].ToString().Trim();
                        exAgent.SetCellValue(i, 20, BEM);
                        string WBFKNEUDAT = dw["WBFKNEUDAT"].ToString().Trim();
                        exAgent.SetCellValue(i, 21, WBFKNEUDAT);
                        string JUDGE = dw["JUDGE"].ToString().Trim();
                        exAgent.SetCellValue(i, 22, JUDGE);
                        */
                        ROLL = WBBSTKNR;
                    }
                                        
                     i = i + 1;
                }
                int count = dtSoruce.Rows.Count;
                exAgent.SetCellValue(1, 1, 1);
                exAgent.BatchConvert2Number(3, 12, count + 2, 12);
                exAgent.BatchConvert2Number(3, 14, count + 2, 15);
                if (LANGUAGE == "V")
                {
                    exAgent.SetCellValue(1, 1, "金陵疵点统计表");
                }
                if (LANGUAGE == "E")
                {
                    exAgent.SetCellValue(1, 1, "Jinling Defect Summary Report");
                }

            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }        
        public override void WriteExcelReport(string fileType)
        {
            bool IsFTP = false;
            try
            {
                strCompany = "JLT";
                strExcelFile = @"JLT\JLTReport.xls";
                if (LANGUAGE == "V")
                {
                    Standard = "FWB18";
                }
                if (LANGUAGE == "E")
                {
                    Standard = "FWB18E";
                }
                TemplatePath = Path.Combine(PathHelper.ApplicationPath, @"Report\JLT\JLTReport.xls");
                destfolder = SetFolder(Root, "成品检验");
                string filedate = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
                string newfilename = @"成品疵点统计" + filedate;
                
                exAgent.CheckWorkBook(newfilename);
                ServerShareFile("", year, month, newfilename, fileType);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void ServerShareFile(string KRS, string year, string month, string ordernumer, string fileType)
        {
            string destfile, tempfile = "";
            string fileabsname = ordernumer;
            try
            {
                filename = destfolder + "\\" + fileabsname + ".xls"; ;
                destfile = filename;
                tempfile = CreateFile(ordernumer);
                if (File.Exists(destfile))
                {
                    File.Delete(destfile);
                }
                File.Move(tempfile, destfile);

                if (fileType == "OpenExcel")
                {
                    System.Diagnostics.Process.Start("EXCEL.EXE", filename);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                if (File.Exists(tempfile))
                {
                    File.Delete(tempfile);
                }
            }
        }
        private string CreateFile(string orderno)
        {
            string filename = "";
            try
            {
                string pwd = "";
                string sheetName = orderno;
                exAgent.Open(TemplatePath);
                exAgent.Company = strCompany;
                exAgent.strExcelFile = strExcelFile;
                exAgent.CreateNewSheet(orderno + ".xls", Standard, sheetName, pwd);
                filename = exAgent.FileName;
                exAgent.SetCurrentSheet(1);
                if (COMBO == "N")
                {
                    FillInExcelData(dsSource, 3, 1);
                }
                else
                {
                    FillInExcel(dsSource, 3, 1);
                }
                exAgent.Save();
                exAgent.Close();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return filename;
        }
        #endregion
    }
}